package com.applink.sell_proof.database.table;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.json.JSONArray;
import org.json.JSONObject;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.util.Log;

import com.applink.sell_proof.EMarketing;
import com.applink.sell_proof.constant.Constant;
import com.applink.sell_proof.constant.ItemStatus;
import com.applink.sell_proof.database.EMarketingContentProvider;
import com.applink.sell_proof.database.EMarketingDatabaseHelper;
import com.applink.sell_proof.model.Claim;
import com.applink.sell_proof.model.ClaimImage;
import com.applink.sell_proof.model.Product;
import com.applink.sell_proof.util.Utility;

public class ClaimDBHelper {
	
	private static ArrayList<Long> idCLaimOld = new ArrayList<Long>();

	private static String TAG = "ClaimDBHelper";

	public static final String TABLE_NAME = "claim_table";
	public static final String ID = "id";
	public static final String STATUS = "claim_status";
	public static final String UPDATED = "claim_updated";

	public static final String PROJECT_ID = "project_id";
	public static final String DISTRIBUTOR_ID = "distributor_id";
	public static final String PROJECT_NAME = "project_name";
	public static final String INVOICE_NO = "claim_invoiceno";
	public static final String TYPE = "claim_type";
	public static final String CUSTOMER_COMPANY_NAME = "claim_customername";
	public static final String DATE = "claim_date";
	public static final String PRODUCTS = "claim_products";
	public static final String IMAGES = "claim_images";
	public static final String TOTAL = "claim_total";
	public static int count = 0;

	public static final String SQL_EXCUTE = "CREATE TABLE " + TABLE_NAME + "("
			+ ID + " integer default 0 ," + Constant.ID_LOCAL
			+ " text default '' ," + PROJECT_ID + " text default '' ,"
			+ DISTRIBUTOR_ID + " integer default 1 ," + PROJECT_NAME
			+ " text default '' ," + CUSTOMER_COMPANY_NAME
			+ " text default '' ," + STATUS + " text default '' ," + TYPE
			+ " text default '' ," + INVOICE_NO + " text default '' ,"
			+ PRODUCTS + " text default '[]' ," + IMAGES
			+ " text default '[]' ," + TOTAL + " real default 0 ,"
			+ " text default '' ," + DATE + " integer default 0 ," + UPDATED
			+ " integer default 1);";

	/**
	 * 
	 * insert/update a project with it associate update status
	 * 
	 * @param p
	 * @param isUpdated
	 * @return
	 */

	public static void deleteDataClaim() {
		// ContentValues values = new ContentValues();
		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();
		resolver.delete(EMarketingContentProvider.CLAIM_CONTENT_URI, null, null);
	}

	public static String insertUpdateClaim(Claim c, boolean isUpdated) {

		boolean isProceeded = false;
		ContentValues values = new ContentValues();
		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();

		String where = "";
		String localid = "";
		long id = c.getId();
		
		if (id != 0) {
			where = ID + "=" + Long.toString(id);
		} else if (!c.getLocalId().equals("")) {
			where = Constant.ID_LOCAL + "='" + c.getLocalId() + "'";
			localid = c.getLocalId();
		} else {
			localid = UUID.randomUUID().toString();
			c.setLocalId(localid);
			values.put(Constant.ID_LOCAL, localid);
		}

		if (c.isUpdated(ID))
			values.put(ID, id);

		if (c.isUpdated(STATUS))
			values.put(STATUS, c.getStatus());

		if (c.isUpdated(INVOICE_NO))
			values.put(INVOICE_NO, c.getInvoiceNo());

		if (c.isUpdated(TYPE))
			values.put(TYPE, c.getType());

		if (c.isUpdated(CUSTOMER_COMPANY_NAME))
			values.put(CUSTOMER_COMPANY_NAME, c.getCustomerCompanyName());

		if (c.isUpdated(DATE))
			values.put(DATE, c.getDate());

		if (c.isUpdated(UPDATED))
			values.put(UPDATED, isUpdated ? 1 : 0);

		if (c.isUpdated(TOTAL))
			values.put(TOTAL, c.getTotal());

		if (c.isUpdated(PROJECT_ID))
			values.put(PROJECT_ID, c.getOpportunityId());

		if (c.isUpdated(PROJECT_NAME))
			values.put(PROJECT_NAME, c.getOpportunityName());

		if (c.isUpdated(DISTRIBUTOR_ID))
			values.put(DISTRIBUTOR_ID, c.getDistributorId());

		setProductsAndImages2View(c, values);

		int rowsE = 0;

		// just call update if necessary
		if (!where.equals("")){
			rowsE = resolver.update(
					EMarketingContentProvider.CLAIM_CONTENT_URI, values, where,
					null);
		
				//isProceeded = true;
			
		}			

		// if this is an update
		if (rowsE != 0) {
			// check if this is an update with localid or not
			if (localid.equals("")) {
				localid = getLocalId(id);
			}

		} else {

			if (localid.equals("")) {
				localid = UUID.randomUUID().toString();
				c.setLocalId(localid);
				values.put(Constant.ID_LOCAL, localid);
				}
			
			/*boolean isExisted = checkExist(c.getId());
			if(isExisted == false){
				//if(isProceeded == false){
				
					idCLaimOld.add(c.getId());
					resolver.insert(EMarketingContentProvider.CLAIM_CONTENT_URI, values);
					
					
				//}	
			}*/
			boolean isExisted = checkExist(c.getId());
			
			if(isExisted == false){
				//if(isProceeded == false) {
					resolver.insert(EMarketingContentProvider.CLAIM_CONTENT_URI, values);
			//	++count;
				//}
			}
			
			
			isProceeded = false;
			values.clear();
			
			//if(c.getId() == 0){
						//}
		}

		
		

		Log.i(TAG, localid + " : created");
		c.setLocalId(localid);
		return localid;
	}
	
	public static boolean checkExist(long id){
		boolean isExist = false;
		List<Claim> claim = ClaimDBHelper.getClaimByStatus("'" + ItemStatus.DRAFT + "','" + ItemStatus.SUBMITTED + "','"
				+ ItemStatus.VERIFIED_SUCCESSFUL + "','"
				+ ItemStatus.VERIFIED_UNSUCCESSFUL + "'");
		
		for(int i=0; i< claim.size();i++){
			Claim claimDetail = claim.get(i);
			System.out.println("Local ID = " + claimDetail.getId() + " ---- Server Id= " + id);
			if(claimDetail.getId() == id){
				return true;
			}
		}
		return isExist;
	}
	
	public static String getLocalId(long id) {

		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();

		Cursor cursor = resolver.query(
				EMarketingContentProvider.CLAIM_CONTENT_URI, null, ID + "=?",
				new String[] { Long.toString(id) }, null);

		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {
					String result = cursor.getString(cursor
							.getColumnIndex(Constant.ID_LOCAL));

					if (!result.equals(""))
						return result;
				}
			} finally {
				cursor.close();
			}
		}
		return "";
	}

	public static void syncFields(String localid, Claim c) {

		ContentValues values = new ContentValues();
		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();

		String where = Constant.ID_LOCAL + "=?";
		String[] selections = new String[] { localid };

		if (c.getId() != 0) {
			values.put(ID, c.getId());
			selections = new String[] { localid, Long.toString(c.getId()) };
			where = Constant.ID_LOCAL + "=? OR " + ID + "=?";
		}

		if (values.size() == 0)
			return;

		setProductsAndImages2View(c, values);

		// set update to updated
		values.put(UPDATED, 1);

		int rowsE = resolver.update(
				EMarketingContentProvider.CLAIM_CONTENT_URI, values, where,
				selections);

		if (rowsE == 1)
			Log.i(TAG, localid + " : sync succeed");
		else
			Log.i(TAG, localid + " : sync failed");
	}

	/**
	 * 
	 * insert list of Projects with the update associate with all of its
	 * 
	 * @param pjList
	 * @param massupdateset
	 */
	public static void insertUpdateClaims(final List<Claim> cList,
			final boolean massupdateset) {
		new Thread() {
			@Override
			public void run() {
				for (Claim c : cList) {
					insertUpdateClaim(c, massupdateset);
				}
			}
		}.start();
	}

	// public static void insertUpdateClaims(final List<Claim> cList) {
	// insertUpdateClaims(cList, true);
	// }

	/**
	 * retrieve a project by localid
	 * 
	 * @param localid
	 * @return
	 */
	public static Claim getClaimByLocalId(String localid) {

		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();

		Cursor cursor = resolver.query(
				EMarketingContentProvider.CLAIM_CONTENT_URI, null,
				Constant.ID_LOCAL + "=?", new String[] { localid }, null);

		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {
					Claim c = createClaimFromCursor(cursor);
					return c;
				}
			} finally {
				cursor.close();
			}
		}
		return null;
	}

	/**
	 * 
	 * get list of projects by one/many status string
	 * 
	 * @param status
	 * @return
	 */
	public static List<Claim> getClaimByStatus(String status) {

		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();

		// select all rows and
		Cursor cursor = resolver.query(
				EMarketingContentProvider.CLAIM_CONTENT_URI, null, STATUS
						+ " IN (" + status + ")", null, null);

		List<Claim> lc = new ArrayList<Claim>();

		if (cursor != null) {
			try {
				while (cursor.moveToNext()) {
					Claim c = createClaimFromCursor(cursor);

					lc.add(c);
				}
			} finally {
				cursor.close();
			}
		}
		return lc;
	}

	private static void setProductsAndImages2View(Claim c, ContentValues v) {
		try {

			// part for products list json
			List<Product> products = c.getProducts();
			JSONArray arrayProducts = new JSONArray();
			if (products != null && products.size() > 0) {

				for (Product product : products) {
					Map<String, String> paramProduct = new HashMap<String, String>();

					paramProduct.put("ProductID",
							Long.toString(product.getProductId()));

					paramProduct.put("ProductClaimID",
							Long.toString(product.getProductClaimId()));

					paramProduct.put("UnitPrice",
							Float.toString(product.getPrice()));

					paramProduct.put("Quantity",
							Integer.toString(product.getQuantity()));
					paramProduct.put("SeriveTag", product.getService_tag());

					paramProduct.put("ModelName", product.getModelName());
					paramProduct.put("ModelNo", product.getModelNo());

					JSONObject jsonProduct = new JSONObject(paramProduct);
					arrayProducts.put(jsonProduct);
				}
			}

			List<ClaimImage> images = c.getImages();
			JSONArray arrayImages = new JSONArray();
			if (images != null && images.size() > 0) {

				for (ClaimImage ci : images) {
					Map<String, String> paramImage = new HashMap<String, String>();

					paramImage.put("ImageID",
							Long.toString(ci.getClaimImageId()));

					paramImage.put("Degree",
							Integer.toString(ci.getRotateDegree()));
					paramImage.put("PhysicalPath", ci.getImageLocation()
							.toString());

					JSONObject jsonImage = new JSONObject(paramImage);
					arrayImages.put(jsonImage);
				}
			}

			if (c.isUpdated(PRODUCTS))
				v.put(PRODUCTS, arrayProducts.toString());

			if (c.isUpdated(IMAGES))
				v.put(IMAGES, arrayImages.toString());

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static List<Claim> getNotUpdatedRecords() {

		// ContentResolver resolver = EMarketing.getInstance()
		// .getApplicationContext().getContentResolver();
		//
		// // select all rows and
		// Cursor cursor = resolver.query(
		// EMarketingContentProvider.CLAIM_CONTENT_URI, null, UPDATED
		// + "=0", null, null);

		SQLiteOpenHelper dbHelper = new EMarketingDatabaseHelper(EMarketing
				.getInstance().getApplicationContext());

		SQLiteDatabase db = dbHelper.getWritableDatabase();

		SQLiteQueryBuilder queryBuildre = new SQLiteQueryBuilder();
		queryBuildre.setTables(TABLE_NAME);

		// set the limiter = 3
		Cursor cursor = queryBuildre.query(db, null, UPDATED + "=0", null,
				null, null, null, "1");

		List<Claim> cls = new ArrayList<Claim>();

		if (cursor != null) {
			try {
				while (cursor.moveToNext()) {
					Claim c = createClaimFromCursor(cursor);

					cls.add(c);
				}
			} finally {
				db.close();
				cursor.close();
			}
		}
		db.close();
		return cls;

	}

	public static int deleteClaim(String localid) {

		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();

		int result = resolver.delete(
				EMarketingContentProvider.CLAIM_CONTENT_URI, Constant.ID_LOCAL
						+ "=? AND " + ID + "=0", new String[] { localid });

		if (result == 0) {
			Log.i(TAG, "Local ID : " + localid
					+ " could not be deleted directly from localstorage.");

			ContentValues values = new ContentValues();

			values.put(STATUS, ItemStatus.DELETE);

			result = resolver.update(
					EMarketingContentProvider.CLAIM_CONTENT_URI, values,
					Constant.ID_LOCAL + "=?", new String[] { localid });

			if (result == 1) {
				Log.i(TAG, "Local ID : " + localid
						+ " status changed to deleted.");
				return 0;
			}
			return 2;
		}

		return result;
	}

	public static List<Claim> searchClaims(String keyword) {
		ContentResolver resolver = EMarketing.getInstance()
				.getApplicationContext().getContentResolver();

		Cursor cursor;

		if (keyword.equals(""))
			cursor = resolver.query(
					EMarketingContentProvider.CLAIM_CONTENT_URI, null, null,
					null, null);
		else
			cursor = resolver.query(
					EMarketingContentProvider.CLAIM_CONTENT_URI, null, "("
							+ CUSTOMER_COMPANY_NAME + " LIKE ?) OR (" + STATUS
							+ " LIKE ?) OR (" + TYPE + " LIKE ?) OR ("
							+ INVOICE_NO + " LIKE ?)", new String[] {
							"%" + keyword + "%", "%" + keyword + "%",
							"%" + keyword + "%", "%" + keyword + "%" }, null);

		List<Claim> cs = new ArrayList<Claim>();

		if (cursor != null) {
			try {
				while (cursor.moveToNext()) {
					Claim c = createClaimFromCursor(cursor);

					cs.add(c);
				}
			} finally {
				cursor.close();
			}
		}
		return cs;
	}

	private static Claim createClaimFromCursor(Cursor cu) {
		Claim c = new Claim();

		c.setId(cu.getInt(cu.getColumnIndex(ID)));
		c.setLocalId(cu.getString(cu.getColumnIndex(Constant.ID_LOCAL)));

		c.setInvoiceNo(cu.getString(cu.getColumnIndex(INVOICE_NO)));
		c.setCustomerCompanyName(cu.getString(cu
				.getColumnIndex(CUSTOMER_COMPANY_NAME)));
		c.setStatus(cu.getString(cu.getColumnIndex(STATUS)));
		c.setType(cu.getString(cu.getColumnIndex(TYPE)));
		c.setDate(cu.getLong(cu.getColumnIndex(DATE)));
		c.setDistributorId(cu.getLong(cu.getColumnIndex(DISTRIBUTOR_ID)));

		c.setOpportunityId(cu.getString(cu.getColumnIndex(PROJECT_ID)));
		c.setOpportunityName(cu.getString(cu.getColumnIndex(PROJECT_NAME)));
		c.setTotal(cu.getDouble(cu.getColumnIndex(TOTAL)));

		try {

			// fetching products list
			JSONArray pa = new JSONArray(cu.getString(cu
					.getColumnIndex(PRODUCTS)));

			for (int i = 0; i < pa.length(); i++) {
				JSONObject jsonProduct = (JSONObject) pa.get(i);

				Product product = new Product();

				product.setModelName(jsonProduct.getString("ModelName"));

				product.setModelNo(jsonProduct.getString("ModelNo"));
				if (jsonProduct.getString("SeriveTag") != null) {
					product.setService_tag(jsonProduct.getString("SeriveTag"));
				}

				String productclaimidS = jsonProduct
						.getString("ProductClaimID");
				if (productclaimidS.length() > 0)
					product.setProductClaimId(Long.parseLong(productclaimidS));

				String quantityS = jsonProduct.getString("Quantity");
				if (quantityS.length() > 0)
					product.setQuantity(Integer.parseInt(quantityS));

				String unitpriceS = jsonProduct.getString("UnitPrice");
				if (unitpriceS.length() > 0)
					product.setPrice(Float.parseFloat(unitpriceS));

				c.getProducts().add(product);
			}

			// fetching images list
			JSONArray ia = new JSONArray(
					cu.getString(cu.getColumnIndex(IMAGES)));

			for (int i = 0; i < ia.length(); i++) {

				JSONObject jsonObject = (JSONObject) ia.get(i);

				ClaimImage image = new ClaimImage();

				image.setImageLocation(Uri.parse(Utility.myURLencode(jsonObject
						.getString("PhysicalPath"))));

				image.setRotateDegree(jsonObject.getInt("Degree"));

				String claimimageidS = jsonObject.getString("ImageID");
				if (claimimageidS.length() > 0)
					image.setClaimImageId(Long.parseLong(claimimageidS));

				c.getImages().add(image);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return c;
	}
}
